<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/1/10
 * Time: 10:55
 */

namespace common\business\kd;


use common\consts\Consts;
use common\helpers\StringUtil;
use common\models\DataTableQueryTrait;
use common\models\Department;
use common\models\kingdee\OrgAdmin;
use common\models\kingdee\Person;
use yii\data\ActiveDataProvider;
use yii\data\Pagination;
use yii\db\ActiveRecord;
use yii\db\Query;
use yii\db\QueryInterface;
use yii\db\Exception;

class KdPersonSearch extends KdPerson
{
    use DataTableQueryTrait;
    /**
     * @var string
     */
    public $org_id;

    /**
     * @param $params array
     * @return array
     */
    public function search4Dt($params){
        $query = $this->getListQuery($params);

        $this->load($params);
        // grid filtering conditions
        $query->andFilterWhere([
            'gender' => $this->gender,
        ]);
        $query->andFilterWhere(['like', 'fid', $this->fid])
            ->andFilterWhere(['like', 'name', $this->name])
            ->andFilterWhere(['like', 'number', $this->number])
            ->andFilterWhere(['like', 'birthday', $this->birthday])
            ->andFilterWhere(['like', 'email', $this->email])
            ->andFilterWhere(['like', 'mobile', $this->mobile])
            ->andFilterWhere(['like', 'id_card_num', $this->id_card_num])
            ->andFilterWhere(['like', 'native_place', $this->native_place])
            ->andFilterWhere(['like', 'blood_type', $this->blood_type])
            ->andFilterWhere(['like', 'wed_status', $this->wed_status])
            ->andFilterWhere(['like', 'nationality', $this->nationality])
            ->andFilterWhere(['like', 'folk', $this->folk])
            ->andFilterWhere(['like', 'hklx', $this->hklx])
            ->andFilterWhere(['like', 'weixin', $this->weixin])
            ->andFilterWhere(['like', 'position_name', $this->position_name])
            ->andFilterWhere(['like', 'position_number', $this->position_number])
            ->andFilterWhere(['like', 'labor_relation_state', $this->labor_relation_state])
            ->andFilterWhere(['like', 'department_name', $this->department_name])
            ->andFilterWhere(['like', 'department_number', $this->department_number])
            ->andFilterWhere(['like', 'department_display_name', $this->department_display_name]);

        $query = $this->getDtQuery($query, $params, [
            'TBP.FNAME_L2',
            'TBP.FNUMBER',
            'TBP.FEMAIL',
            'TOA.FDISPLAYNAME_L2'
        ]);

        $result = [
            'data' => $this->getDtListQuery($query, $params)->all(self::getDb()),
            'recordsTotal' => $query->count('*', self::getDb()),
            'recordsFiltered' => $query->count('*', self::getDb()),
        ];

        return $result;
    }


    /**
     * @param $params
     * @return \yii\db\QueryInterface
     */
    public function getListQuery($params){
        $id = '';
        if (key_exists('id', $params)){
            $id = $params['id'];
        }

        if (key_exists('department_id', $params)){
            $department_id = $params['department_id'];
            if (!StringUtil::isNullOrWhiteSpace($department_id)){
                $topDepartment = Department::findOne($department_id);
                $topOrg = OrgAdmin::findOne([
                    'FNUMBER' => $topDepartment->number,
                ]);
                $id = $topOrg->FID;
            }
        }

        if(StringUtil::isNullOrWhiteSpace($id)){
            $topOrg = OrgAdmin::findOne([
                'FPARENTID' => null,
            ]);
            $id = $topOrg->FID;
        }

        $query = $this->getPersonByOrgQuery($id);
        return $query;
    }

    /**
     * Creates data provider instance with search query applied
     * @param string $orgId
     *
     * @return QueryInterface
     */
    public function getPersonByOrgQuery($orgId){
        $db = OrgAdmin::getDb();
        $orgIdParams = [
            'fid' => $orgId
        ];
        $cmd = $db->createCommand('SELECT t.fid FROM T_ORG_ADMIN t START WITH t.fid = :fid CONNECT BY t.fparentid = PRIOR fid', $orgIdParams);
//        $cmd->bindParam('fid', $orgId);
        try {
            $orgIdArr = $cmd->queryColumn();
        } catch (Exception $e) {
            return null;
        }
//        $query =  Person::find()->alias('TBP')
        $query = (new Query())->from('T_BD_PERSON TBP')
            ->leftJoin('T_BD_HRWED TBWED', 'TBWED.FID = TBP.FWEDID')
            ->leftJoin('T_BD_NATIONALITY NAT', 'NAT.FID = TBP.FNATIONALITYID')
            ->leftJoin('T_BD_HRFOLK FK', 'FK.FID = TBP.FFOLKID')
            ->leftJoin('T_BD_REGPERMRESIDENCE REGP', 'REGP.FID = TBP.FREGRESIDENCEID')
            ->leftJoin('T_HR_PERSONCONTACTMETHOD THPCM', 'THPCM.FPERSONID = TBP.FID')
            ->leftJoin('T_HR_PERSONPOSITION THPP', 'THPP.FPERSONID = TBP.FID')
            ->leftJoin('T_ORG_POSITION TOPP', 'TOPP.FID = THPP.FPRIMARYPOSITIONID')
            ->leftJoin('T_HR_EMPLABORRELATION THELR', 'THELR.FPERSONID = TBP.FID')
            ->leftJoin('T_HR_BDEMPLOYEETYPE THBET', 'THBET.FID = THELR.FLABORRELATIONSTATEID')
            ->leftJoin('T_ORG_ADMIN TOA', 'TOA.FID = TOPP.FADMINORGUNITID')
            ->andFilterWhere(['in' , 'THPP.FPERSONDEP', $orgIdArr])
            ->select([
                'fid' => 'TBP.FID',
                'name' => 'TBP.FNAME_L2',
                'number' => 'TBP.FNUMBER',
                'gender' => 'TBP.FGENDER',
                'birthday' => 'to_char(TBP.FBIRTHDAY , \'yyyy-MM-dd\')',
                'email' => 'TBP.FEMAIL',
                'mobile' => 'TBP.FCELL',
                'id_card_num' => 'TBP.FIDCARDNO',
                'native_place' => 'TBP.FNATIVEPLACE_L2',
                'blood_type' => 'TBP.FBLOODTYPE',
                'wed_status' => 'TBWED.FNAME_L2',
                'nationality' => 'NAT.FNAME_L2',
                'folk' => 'FK.FNAME_L2',
                'hklx' => 'REGP.FNAME_L2',
                'weixin' => 'THPCM.FWEIXIN',
                'position_name' => 'TOPP.FNAME_L2',
                'position_number' => 'TOPP.FNUMBER',
                'labor_relation_state' => 'THBET.FNAME_L2',
                'department_name' => 'TOA.FNAME_L2',
                'department_number' => 'TOA.FNUMBER',
                'department_display_name' => 'TOA.FDISPLAYNAME_L2',
                'department_simple_name' => 'TOA.FSIMPLENAME',
            ]);
        return $query;
    }
}